package com.creditease.mobile.rss.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.creditease.mobile.rss.RssConst;
import com.creditease.mobile.rss.model.RssItemModel;

public class RssItemDB extends SQLiteOpenHelper {

	private final static String DB_NAME = "RSSITEM";
	private final static int version = 1;

	public RssItemDB(Context context) {
		super(context, DB_NAME, null, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql = "CREATE TABLE rss_item (id integer primary key autoincrement, articleAuthorName,articleContent,articleLink,"
						+ "articleTime,articleTitle,authorAvatar,authorName,content,favTime,image,isFav,isRead,sectionId,time)";
		db.execSQL(sql);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}

	public synchronized void save(RssItemModel item) {
		SQLiteDatabase db = getWritableDatabase();
		try {

			String sql = "insert into rss_item (articleAuthorName,"
							+ "articleContent,"
							+ "articleLink,"
							+ "articleTime,"
							+ "articleTitle,"
							+ "authorAvatar,"
							+ "authorName,"
							+ "content,"
							+ "favTime,"
							+ "image,"
							+ "isFav,"
							+ "isRead,"
							+ "sectionId,time) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			db.execSQL(sql, new Object[]{	item.articleAuthorName,
											item.articleContent,
											item.articleLink,
											item.articleTime,
											item.articleTitle,
											item.authorAvatar,
											item.authorName,
											item.content,
											item.favTime,
											item.image,
											item.isFav,
											item.isRead,
											item.sectionId,
											item.time});
		}
		finally {

			db.close();
		}
	}

	public synchronized void update(RssItemModel model) {
		SQLiteDatabase db = getWritableDatabase();
		try {

//			String sql = "update rss_item set isRead=? where id=?";
			ContentValues cv=new ContentValues();		
			cv.put("isRead", model.isRead);
			cv.put("isFav", model.isFav);
			cv.put("favTime", model.favTime);
			db.update("rss_item", cv, "id=?", new String[]{model.id});		
		}
		finally {

			db.close();
		}
	}

	public synchronized void delete(Object id) {
		SQLiteDatabase db = getWritableDatabase();
		String sql = "delete from rss_item  where id=?";
		db.execSQL(sql, new Object[]{id});
	}

	public synchronized List<RssItemModel> findAll() {
		SQLiteDatabase db = getReadableDatabase();
		List<RssItemModel> result = new ArrayList<RssItemModel>();
		String sql = "select * from rss_item";
		Cursor cursor = null;
		try {
			cursor = db.rawQuery(sql, null);
			while (cursor.moveToNext()) {

				int id = cursor.getInt(0); // 获取第一列的值,第一列的索引从0开始
				String articleAuthorName = cursor.getString(1);
				String articleContent = cursor.getString(2);
				String articleLink = cursor.getString(3);
				long articleTime = cursor.getLong(4);
				String articleTitle = cursor.getString(5);
				String authorAvatar = cursor.getString(6);
				String authorName = cursor.getString(7);
				String content = cursor.getString(8);
				long favTime = cursor.getLong(9);
				String image = cursor.getString(10);
				boolean isFav = cursor.getInt(11) == 1 ? true : false;
				boolean isRead = cursor.getInt(12) == 1 ? true : false;
				String sectionId = cursor.getString(13);
				long time = cursor.getLong(14);
				RssItemModel model = new RssItemModel(	articleAuthorName,
														articleContent,
														articleLink,
														articleTime,
														articleTitle,
														authorAvatar,
														authorName,
														content,
														favTime,
														image,
														isFav,
														isRead,
														sectionId,
														time);
				result.add(model);

			}
		}
		finally {
			if (cursor != null) {
				cursor.close();
			}
			db.close();
		}
		return result;

	}

	public synchronized List<RssItemModel> findByPart(String sectionId, int offset, int max) {
		if(RssConst.FAV_ID.equals(sectionId)){
			return findFavByPart(sectionId, offset, max);
		}
		SQLiteDatabase db = getReadableDatabase();
		List<RssItemModel> result = new ArrayList<RssItemModel>();
		String sql = "select * from rss_item where sectionId=?  order by articleTime desc limit ?,?";
		Cursor cursor = null;
		try {
			cursor = db.rawQuery(	sql,
									new String[]{	sectionId,
													String.valueOf(offset),
													String.valueOf(max)});
			while (cursor.moveToNext()) {

				int id = cursor.getInt(0); // 获取第一列的值,第一列的索引从0开始
				String articleAuthorName = cursor.getString(1);
				String articleContent = cursor.getString(2);
				String articleLink = cursor.getString(3);
				long articleTime = cursor.getLong(4);
				String articleTitle = cursor.getString(5);
				String authorAvatar = cursor.getString(6);
				String authorName = cursor.getString(7);
				String content = cursor.getString(8);
				long favTime = cursor.getLong(9);
				String image = cursor.getString(10);
				boolean isFav = cursor.getInt(11) == 1 ? true : false;
				boolean isRead = cursor.getInt(12) == 1 ? true : false;
				// String sectionId = cursor.getString(13);
				long time = cursor.getLong(14);
				RssItemModel model = new RssItemModel(	articleAuthorName,
														articleContent,
														articleLink,
														articleTime,
														articleTitle,
														authorAvatar,
														authorName,
														content,
														favTime,
														image,
														isFav,
														isRead,
														sectionId,
														time);
				model.id = String.valueOf(id);
				result.add(model);
			}
		}
		finally {
			if (cursor != null) {
				cursor.close();
			}
			db.close();
		}
		return result;

	}
	public synchronized List<RssItemModel> findFavByPart(String sectionId, int offset, int max) {
		SQLiteDatabase db = getReadableDatabase();
		List<RssItemModel> result = new ArrayList<RssItemModel>();
		String sql = "select * from rss_item where isFav=1  order by favTime desc limit ?,?";
		Cursor cursor = null;
		try {
			cursor = db.rawQuery(	sql,
									new String[]{	
													String.valueOf(offset),
													String.valueOf(max)});
			while (cursor.moveToNext()) {

				int id = cursor.getInt(0); // 获取第一列的值,第一列的索引从0开始
				String articleAuthorName = cursor.getString(1);
				String articleContent = cursor.getString(2);
				String articleLink = cursor.getString(3);
				long articleTime = cursor.getLong(4);
				String articleTitle = cursor.getString(5);
				String authorAvatar = cursor.getString(6);
				String authorName = cursor.getString(7);
				String content = cursor.getString(8);
				long favTime = cursor.getLong(9);
				String image = cursor.getString(10);
				boolean isFav = cursor.getInt(11) == 1 ? true : false;
				boolean isRead = cursor.getInt(12) == 1 ? true : false;
				// String sectionId = cursor.getString(13);
				long time = cursor.getLong(14);
				RssItemModel model = new RssItemModel(	articleAuthorName,
														articleContent,
														articleLink,
														articleTime,
														articleTitle,
														authorAvatar,
														authorName,
														content,
														favTime,
														image,
														isFav,
														isRead,
														sectionId,
														time);
				model.id = String.valueOf(id);
				result.add(model);

			}
		}
		finally {
			if (cursor != null) {
				cursor.close();
			}
			db.close();
		}
		return result;

	}
	public synchronized List<RssItemModel> findBySectionId(String sectionId) {
		SQLiteDatabase db = getReadableDatabase();
		List<RssItemModel> result = new ArrayList<RssItemModel>();
		String sql = "select * from rss_item where sectionId=?  order by articleTime desc ";
		Cursor cursor = null;
		try {
			cursor = db.rawQuery(sql, new String[]{sectionId});
			while (cursor.moveToNext()) {

				int id = cursor.getInt(0); // 获取第一列的值,第一列的索引从0开始
				String articleAuthorName = cursor.getString(1);
				String articleContent = cursor.getString(2);
				String articleLink = cursor.getString(3);
				long articleTime = cursor.getLong(4);
				String articleTitle = cursor.getString(5);
				String authorAvatar = cursor.getString(6);
				String authorName = cursor.getString(7);
				String content = cursor.getString(8);
				long favTime = cursor.getLong(9);
				String image = cursor.getString(10);
				boolean isFav = cursor.getInt(11) == 1 ? true : false;
				boolean isRead = cursor.getInt(12) == 1 ? true : false;
				// String sectionId = cursor.getString(13);
				long time = cursor.getLong(14);
				RssItemModel model = new RssItemModel(	articleAuthorName,
														articleContent,
														articleLink,
														articleTime,
														articleTitle,
														authorAvatar,
														authorName,
														content,
														favTime,
														image,
														isFav,
														isRead,
														sectionId,
														time);
				model.id = String.valueOf(id);
				result.add(model);

			}
		}
		finally {
			if (cursor != null) {
				cursor.close();
			}
			db.close();
		}
		return result;

	}

	public int countBySectionId(String sectionId) {
		if(RssConst.FAV_ID.equals(sectionId)){
			return countFavBySectionId(sectionId) ;
		}
		SQLiteDatabase db = getReadableDatabase();
		String sql = "select count(*) from rss_item where sectionId=? ";
		Cursor cursor = null;
		int id = 0;
		try {
			cursor = db.rawQuery(sql, new String[]{sectionId});
			if (cursor.moveToNext()) {
				id = cursor.getInt(0); // 获取第一列的值,第一列的索引从0开始
			}
		}
		finally {
			if (cursor != null) {
				cursor.close();
			}
			db.close();
		}
		return id;

	}
	public int countFavBySectionId(String sectionId) {
		
		SQLiteDatabase db = getReadableDatabase();
		String sql = "select count(*) from rss_item where isFav=1 ";
		Cursor cursor = null;
		int id = 0;
		try {
			cursor = db.rawQuery(sql, null);
			if (cursor.moveToNext()) {
				id = cursor.getInt(0); // 获取第一列的值,第一列的索引从0开始
			}
		}
		finally {
			if (cursor != null) {
				cursor.close();
			}
			db.close();
		}
		return id;

	}
	/**
	 * Description:
	 * 
	 * @param
	 * @return RssModel
	 * @throws
	 * @Author liong Create Date: 2012-3-19 下午1:29:22
	 */

	public RssItemModel findById(String id) {
		Log.i("A", "id=" + id);
		String sql = "select * from rss_item where id=?";
		Cursor cursor = null;
		SQLiteDatabase db = getReadableDatabase();
		try {
			cursor = db.rawQuery(sql, new String[]{id});
			if (cursor.moveToNext()) {
				String articleAuthorName = cursor.getString(1);
				String articleContent = cursor.getString(2);
				String articleLink = cursor.getString(3);
				long articleTime = cursor.getLong(4);
				String articleTitle = cursor.getString(5);
				String authorAvatar = cursor.getString(6);
				String authorName = cursor.getString(7);
				String content = cursor.getString(8);
				long favTime = cursor.getLong(9);
				String image = cursor.getString(10);
				boolean isFav = cursor.getInt(11) == 1 ? true : false;
				boolean isRead = cursor.getInt(12) == 1 ? true : false;
				String sectionId = cursor.getString(13);
				long time = cursor.getLong(14);
				RssItemModel model = new RssItemModel(	articleAuthorName,
														articleContent,
														articleLink,
														articleTime,
														articleTitle,
														authorAvatar,
														authorName,
														content,
														favTime,
														image,
														isFav,
														isRead,
														sectionId,
														time);
				model.id = String.valueOf(id);
				return model;
			}
			return null;
		}
		finally {
			if (cursor != null) {
				cursor.close();
			}
			db.close();
		}

	}

	public void deleteBySectionId(String sectionId) {
		SQLiteDatabase db = getWritableDatabase();
		try {
			String sql = "delete from rss_item  where sectionId=?";
			db.execSQL(sql, new String[]{sectionId});
		}
		finally {
			db.close();
		}
	}
}
